﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;
namespace DAL
{
    public class HouseOrderService
    {
        
        public static int  AddOrder(HouseOrder order){

            string strSql = "insert into HouseOrder values(" 
                
                + order.ClientId + "," 
                + order.HouseId + ","
                +order.StatusCodeId+","
                +order.IsClientConfirmed+","
                +order.IsHouseHolderConfirmed+");";
            return DBHelper.ExecuteCommand(strSql);
        }

        public static bool HasOrdered(int clientId, int houseId)
        {
            string sql = string.Format("select * from HouseOrder where ClientId={0} and HouseId={1}", clientId, houseId);
            if (GetHouseOrderBySql(sql) == null)
                return false;
            else
                return true;
        }

        public static HouseOrder GetHouseOrderById(int id)
        {
            string sql = string.Format("select * from HouseOrder where Id={0}", id);
            return GetHouseOrderBySql(sql);
        }

        private static HouseOrder GetHouseOrderBySql(string sql)
        {
            List<HouseOrder> list = GetHouseOrdersBySql(sql);
            if (list.Count() > 0)
                return list[0];
            return null;
        }

        public static List<HouseOrder> GetAllHouseOrdersByHouseId(int houseId)
        {
            string sql = string.Format("select * from HouseOrder where HouseId={0}", houseId);
            return GetHouseOrdersBySql(sql);
        }

        private static List<HouseOrder> GetHouseOrdersBySql(string sql)
        {
            List<HouseOrder> list = new List<HouseOrder>();
            DataTable dt = DBHelper.GetDataSet(sql);
            foreach (DataRow row in dt.Rows)
            {
                HouseOrder hOrder = new HouseOrder();
                hOrder.Id = (int)row["Id"];
                hOrder.HouseId = (int)row["HouseId"];
                hOrder.ClientId = (int)row["ClientId"];
                hOrder.StatusCodeId = (int)row["StatusCodeId"];
                hOrder.IsClientConfirmed = (int)row["IsClientConfirmed"];
                hOrder.IsHouseHolderConfirmed = (int)row["IsHouseHolderConfirmed"];
                list.Add(hOrder);
            }
            return list;
        }

        public static List<HouseOrder> GetHouseOrdersByUserId(int userId)
        {
            string sql = string.Format("select * from HouseOrder where HouseId in (select Id from House where HouseHolderId={0}) order by Id desc", userId);
            return GetHouseOrdersBySql(sql);
        }

        public static int Count()
        {
            string sql = string.Format("select count(*) from HouseOrder");
            return DBHelper.GetScalar(sql);
        }


        public static List<HouseOrder> GetHouseOrderByClientId(int clientId)
        {
            string sql = string.Format("select * from HouseOrder where clientId={0} order by Id desc", clientId);
            return GetHouseOrdersBySql(sql);
        }

        public static List<HouseOrder> GetHouseOrderByHouseId(int houseId)
        {
            string sql = string.Format("select * from HouseOrder where HouseId={0}", houseId);
            return GetHouseOrdersBySql(sql);
        }
    }
}
